# coding=utf-8
import json
import MySQLdb
import sys

reload(sys)
sys.setdefaultencoding('utf-8')

Host = "118.31.32.197"
Port = 3306
User = "gmj"
Passwd = "123456"
Db = "worldcup"

conn = MySQLdb.connect(host=Host, port=Port, user=User, passwd=Passwd, db=Db, charset="utf8")
cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)

cursor.execute("select * from userModel_spu where spu_code = 100025")
sizes = ["XS", "S", "M", "L", "XL", "XXL"]
# numbers = ["不印号","7-DRAXLER", "8-KROOS", "10-ÖZIL", "13-MÜLLER", "14-CAN"] # 德国
numbers = ["不印号","10-MESSI","21-DYBALA","9-KUN AGÜERO"] #阿根廷
# numbers = ["不印号","10-MESSI"] #阿根廷
# numbers = ["不印号","3-PIQUE","6-A. INIESTA","7-MORATA","15-RAMOS","21-SILVA","22-ISCO"] #xibanya
rows = cursor.fetchall()
for row in rows:
	spu_code = row["spu_code"]
	sku_name = row["spu_name"]
	sku_images = row["spu_image"]
	i = 1
	for size in sizes:
		for number in numbers:
			if i < 10:
				sku_code = str(spu_code) + "00" + str(i)
			else:
				sku_code = str(spu_code) + "0" + str(i)
			sku_specification = json.dumps({
				"size": size,
				"mark": number
			}, )
			origin_price = "599" if number == "不印号" else "799"
			price = "528" if number == "不印号" else "728"
			sql = "insert into userModel_sku(sku_code,sku_name,spu_code,sku_images,sku_specification,origin_price, price) " \
					  "values ({}, '{}', {}, '{}', '{}',{},{});".format(sku_code, str(sku_name), str(spu_code), sku_images, sku_specification.decode('unicode_escape'), origin_price, price)
			print sql
			i = i + 1




# cursor.execute("select * from userModel_spu where spu_code>= 100039 and spu_code <= 100046")
# rows = cursor.fetchall()
# for row in rows:
# 	property = row["spu_ext_properties"]
# 	spu_code = row["spu_code"]
# 	pp_dict = json.loads(property)
# 	i = 1
# 	for value in pp_dict.get("sizes"):
# 		key = "size"
# 		sku_code = str(spu_code) + "00" + str(i)
# 		sku_name = row["spu_name"]
# 		sku_specification = json.dumps({
# 			key: value
# 		}, )
# 		i += 1
# 		sql = "insert into userModel_sku(sku_code,sku_name,spu_code,sku_specification,price) " \
# 			  "values ({}, '{}', {}, '{}', {})".format(sku_code, str(sku_name), str(spu_code), sku_specification, "0.01")
# 		print sql
# 		cursor.execute(sql)
# 		cursor.execute("commit")

# cursor.execute("select * from userModel_spu where spu_code=100048")
# rows = cursor.fetchall()
# for row in rows:
# 	property = row["spu_ext_properties"]
# 	spu_code = row["spu_code"]
# 	pp_dict = json.loads(property)
# 	country_list = pp_dict.get("country")
# 	size_list = pp_dict.get("size")
#
# 	i = 1
# 	for country in country_list:
# 		for size in size_list:
# 			if i < 10:
# 				sku_code = str(spu_code) + "00" + str(i)
# 			else:
# 				sku_code = str(spu_code) + "0" + str(i)
# 			sku_name = country + "国旗 " + size
# 			sku_specification = json.dumps({
# 					"country": country,"size":size
# 			}, )
# 			# print sku_specification.decode("unicode-escape")
# 			i += 1
# 			sql = "insert into userModel_sku(sku_code,sku_name,spu_code,sku_specification,price) " \
# 					  "values ({}, '{}', {}, '{}', {})".format(sku_code, str(sku_name), str(spu_code), sku_specification.decode("unicode-escape"), "0.01")
# 			print sql
# 			cursor.execute(sql)
# 			cursor.execute("commit")
